/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package sunlight.database;

import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import sunlight.core.Application;

import org.apache.commons.lang.StringEscapeUtils;

import sunlight.logger.EventLogger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map.Entry;
import java.util.Set;

/**
 * Clasa utila care simplifica interactiunea cu baza de date
 * Aceasta este optimizata pentru bazele de date de tip MySQL; 
 * Pentru a putea fi utilizata ai nevoie de clasele 
 * org.apache.commons.lang.StringEscapeUtils
 * com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
 * com.mysql.jdbc.jdbc2.optional.MysqlDataSource
 * &raquo; respectiv pachetele Apache Commons Lang si MySql JDBC Connector care se pot descarca de aici:
 * <a href="http://www.mysql.com/downloads/connector/j/"> http://www.mysql.com/downloads/connector/j/ </a>
 * <a href="http://commons.apache.org/lang/"> http://commons.apache.org/lang/ </a>
 * 
 * Exemplu:
 * <pre>
 * 		MySqlDatabase Database = new MySqlDatabase("127.0.0.1", "localuser", "abcdpassword", "prog_database");
 * 		if(Database.isConnected()){
 * 			...efectueaza cereri sql...
 * 		}
 * </pre>
 *
 * @author Adrian Şilimon
 * @version 1.1
 */
public class MySqlDatabase {

	/**
	 * campuri pentru relizarea si reprezentarea conexiunii
	 */
    private Connection      DBConnection;
    private MysqlDataSource DBase;

    /**
     * Statement : retine ultima cerere construita pentru a fi trimisa SGBD-ului
     * query	 : forma String a cererii
     * error	 : ultima eroare intalnita
     * Result	 : rezultatul ultimei cereri de tip SELECT primite de la SGBD
     */
    private PreparedStatement Statement;
    
    public String            query;
    public String            error;
    public ResultSet         Result;
    
    public int QUERYTYPE_INSERT	= 1;
    public int QUERYTYPE_SELECT	= 2;
    public int QUERYTYPE_UPDATE	= 3;
    public int QUERYTYPE_DELETE	= 4;

    
    public String FILTEROPERATOR_CONTAINS 	   = "LIKE '%<value>%'";
    public String FILTEROPERATOR_STARTSWITH    = "LIKE '<value>%'";
    public String FILTEROPERATOR_ENDSWITH 	   = "LIKE '%<value>'";
    public String FILTEROPERATOR_BIGGER 	   = ">";
    public String FILTEROPERATOR_BIGGEROREQUAL = ">=";
    public String FILTEROPERATOR_SMALLER 	   = "<";
    public String FILTEROPERATOR_SMALLEROREQUAL= "<=";
    
    public String SELECTCONDITION_OR  = " OR ";
    public String SELECTCONDITION_AND = " AND ";
    
    /**
     * constructor explicit, realizeaza si conexiunea cu baza de date pe baza parametrilor
     * 
     * @param host		Host-ul sau adresa la care se gaseste baza de date
     * @param port		port-ul pe care se face conexiunea (uzual 3036)
     * @param username	numele de utilizator
     * @param password	parola	
     * @param name		numele bazei de date
     * @param charset	set-ul de caractere pentru conexiune (recomandat utf8)
     * @param encoding	codarea tebelelor bazei de date (recomandat utf-8) 
     */
    public MySqlDatabase(String host, int port, String username, String password, String name, String charset, String encoding){
    	this.DBConnection = this.connect(host, port, username, password, name, charset, encoding);
    }

    /**
     * constructor explicit, realizeaza si conexiunea cu baza de date pe baza parametrilor;
     * Acesta va incerca o conexiune pe port-ul 3036 cu setul de caractere utf8 si codarea utf-8 
     * 
     * @param host		host-ul sau adresa la care se gaseste baza de date
     * @param username	numele de utilizator
     * @param password	parola utilizatorului
     * @param name		numele bazei de date
     */
    public MySqlDatabase(String host, String username, String password, String name){
        this.DBConnection = this.connect(host, 3036, username, password, name, "utf8", "utf-8");
    }

    /**
     * constructor explicit, realizeaza si conexiunea cu baza de date pe baza parametrilor;
     * acesta permite specificarea setului de caractere pentru conexiune; Util daca vrem sa limitam
     * setul de caractere la un set diferit de utf8;
     * 
     * @param host		host-ul sau adresa la care se gaseste baza de date
     * @param username	numele de utilizator
     * @param password	parola utilizatorului
     * @param name		numele bazei de date
     * @param charset	set-ul de caractere pentru conexiune
     */
    public MySqlDatabase(String host, String username, String password, String name, String charset){
        this.DBConnection = this.connect(host, 3036, username, password, name, charset, "utf-8");
    }

    /**
     * constructor explicit, realizeaza si conexiunea cu baza de date pe baza parametrilor;
     * Acesta va incerca o conexiune pe port-ul 3036 cu setul de caractere utf8 si codarea utf-8,
     * presupunand ca numele bazei de date este acelas cu cel de utilizator;
     * 
     * @param host		host-ul sau adresa la care se gaseste baza de date
     * @param username	numele de utilizator
     * @param password	parola utilizatorului
     */
    public MySqlDatabase(String host, String username, String password){
        this.DBConnection = this.connect(host, 3036, username, password, username, "utf8", "utf-8");
    }

    /**
     * realizeaza si conexiunea cu baza de date pe baza parametrilor si returneaza obiectul java.sql.Connection corespunzator
     * 
     * @param host		host-ul sau adresa la care se gaseste baza de date
     * @param port		port-ul pe care se face conexiunea
     * @param username	numele de utilizator
     * @param password	parola utilizatorului
     * @param name		numele bazei de date
     * @param charset	set-ul de caractere pentru conexiune
     * @param encoding	codarea tebelelor bazei de date
     * @return Connection obiectul de tip conexiune asociat sau null in cazul aparitiei unei exceptii SQLException
     */
    public Connection connect(String host, int port, String username, String password, String name, String charset, String encoding){
        this.DBase = new MysqlDataSource();
        this.DBase.setUrl("jdbc:mysql://"+ host +"/"+ name);
        this.DBase.setPort(port);
        this.DBase.setUser(username);
        this.DBase.setPassword(password);
        this.DBase.setCharacterSetResults(charset);
        this.DBase.setCharacterEncoding(encoding);


        try{
        	return this.DBase.getConnection();
		}
		catch(SQLException e){
		   Application.Log.write("Nu pot realiza o conexiune cu baza de date.", EventLogger.ERROR);
		}
		
		return null;
    }

    /**
     * returneaza starea conexiunii respectiv daca s-a reusit conectarea cu SGBD-ul sau nu
     * 
     * @return boolean existenta conexiunii la baza de date
     */
    public boolean isConnected(){
        return this.DBConnection != null ? true : false;
    }

    /**
     * executa o cerere sql si returneaza obiectul rezultat in urma cererii
     * Atentie! rezultatul trebuie convertit la ResultSet in cazul in care aceasta este o cerere de tip SELECT
     * 
     * Exemplu:
     * <pre>
     * 		ResultSet data = (ResultSet) Database.executeQuery("SELECT * FROM `atable` WHERE `id`=777", Database.QUERYTYPE_SELECT, true);
     * </pre>
     * 
     * @param query		String-ul ca contine cererea;
     * @param type		tipul cererii (de forma MySqlDatabase.QUERYTYPE_...)
     * @param escape	specifica daca cerea va fi sanitizata sau nu
     * @return			obiectul rezultat in urma cererii sau null in cazul aparitiei unei exceptii de tipul SQLException
     */
    public Object executeQuery(String query, int type, boolean escape){

    	if(escape){
    		query = StringEscapeUtils.escapeSql(query);
    	}

    	this.query = query;
    	
    	try {
			this.Statement = this.DBConnection.prepareStatement(this.query);		
			if(type == this.QUERYTYPE_SELECT){
	    		this.Result = this.Statement.executeQuery();
	    		
	    		return this.Result;
	    	}
			else{
				int r = this.Statement.executeUpdate();
				return new Integer(r);
			}
			
		} catch (SQLException e) {
			Application.Log.write(e.toString(), EventLogger.ERROR);
		}
    	
    	
		return null;
    }
    
    /**
     * executa o cerere si returneaza obiectul asociat;
     * cererea va fi sanitizata implicit inainte de executie
     * 
     * @see MySqlDatabase.executeQuery(String, int, boolean);
     * 
     * @param query	cererea SQL
     * @param type	tipul cererii (de forma MySqlDatabase.QUERYTYPE_...)
     * @return
     */
    public Object executeQuery(String query, int type){
    	return this.executeQuery(query, type, true);
    }
    
    /**
     * executa o cerere SQL si returneaza obiectul rezultat;
     * acesta determnina automat tipul cererii;
     * 
     * @see MySqlDatabase.executeQuery(String, int, boolean);
     * 
     * @param query		cererea SQL sub forma unui String
     * @param escape	specifica daca cerea va fi sanitizata sau nu
     * @return			obiectul rezultat, sau null in cazul in care tipul cererii nu este recunoscut
     */
    public Object executeQuery(String query, boolean escape){
    	
    	String lowered = query.toLowerCase();
    	
    	if(lowered.indexOf("select") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_SELECT, escape);
    	}
    	if(lowered.indexOf("insert") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_INSERT, escape);
    	}
    	if(lowered.indexOf("update") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_UPDATE, escape);
    	}
    	if(lowered.indexOf("delete") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_DELETE, escape);
    	}
    	
    	return null;
    }
    
    /**
     * executa o cerere SQL si returneaza obiectul rezultat;
     * acesta determnina automat tipul cererii si sanitizeaza string-ul cererii;
     * 
     * @see MySqlDatabase.executeQuery(String, int, boolean);
     * 
     * @param query		cererea SQL sub forma unui String
     * @return			obiectul rezultat, sau null in cazul in care tipul cererii nu este recunoscut
     */
    public Object executeQuery(String query){
    	
    	String lowered = query.toLowerCase();
    	
    	if(lowered.indexOf("select") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_SELECT, true);
    	}
    	if(lowered.indexOf("insert") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_INSERT, true);
    	}
    	if(lowered.indexOf("update") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_UPDATE, true);
    	}
    	if(lowered.indexOf("delete") < lowered.indexOf(" ")){
    		return this.executeQuery(query, this.QUERYTYPE_DELETE, true);
    	}
    	
    	return null;
    }
    
    /**
     * adauga conditii la cererea sql sub forma 
     * <pre>preconditie `camp_1`='valoare_1' subconditie `camp_2`='valoare_2' ... subconditie `camp_n`='valoare_n'</pre>
     * unde camp_i si valoare_i sunt specificate ca perechile de camp=>valoare ale unui obiect HashMap
     *
     * @param precond		preconditia (d.e WHERE)
     * @param conditions	conditiile (colectie, camp=valoare...)
     * @param subcond		subconditie (d.e AND sau OR)
     */
    private void addConditions(String precond, HashMap<?, ?> conditions, String subcond){
        Iterator<?> iterator;
        Entry<?, ?> entry;

        if((conditions != null) && (!conditions.isEmpty())){

            String field;
            String value;

            String  cstring         = "";
            Set<?>  whereConditions = conditions.entrySet();

            iterator = whereConditions.iterator();
            while(iterator.hasNext()){
                 entry = (Entry<?, ?>)iterator.next();

                 //-------sanitizeaza string-urile------------//
                 field = StringEscapeUtils.escapeSql(entry.getKey().toString());
                 value = StringEscapeUtils.escapeSql(entry.getValue().toString());
                 
                 cstring += cstring.length() == 0 ? " "+ precond +" `"+ field +"`='"+ value + "'" : " "+ subcond +" `"+ field +"`='"+value+"'";
            }

            this.query+= cstring;
        }
    }

    /**
     * adauga conditii cererii sql
     * 
     * @see MySqlDatabase.addConditions(String precond, HashMap conditions, String subcond)
     * 
     * @param precond		preconditia (d.e WHERE) 
     * @param conditions	conditiile (colectie, camp=valoare...) 
     */
    private void addConditions(String precond, HashMap<?, ?> conditions){
        this.addConditions(precond, conditions, precond);
    }

    /**
     * atribuie valorile din HashMap campurilor corespunzatoare tabelului &quot;table&quot;
     * contruieste un string de forma <pre>tabel.camp_1=valoare_1, ... tabel.camp_n=valoare_n</pre> 
     * 
     * @param table		tabelul 
     * @param values	valorile de adaugat (forma camp=valoare)
     */
    private void addFieldsValues(String table, HashMap<?, ?> values){
        Iterator<?> iterator;
        Entry<?, ?> entry;

        if((values != null) && (!values.isEmpty())){
            String tbl     = StringEscapeUtils.escapeSql(table);
            String field;
            String value;

            String  fvstring        = "";
            Set<?>  fieldsValues = values.entrySet();

            iterator = fieldsValues.iterator();
            while(iterator.hasNext()){
                 entry = (Entry<?, ?>)iterator.next();

                 //-------sanitizeaza string-urile------------//
                 field = StringEscapeUtils.escapeSql(entry.getKey().toString());
                 value = StringEscapeUtils.escapeSql(entry.getValue().toString());

                 fvstring += fvstring.length() == 0 ? " `"+ tbl +"`.`"+ field +"`='"+ value + "'" : ", `"+ tbl +"`.`"+ field +"`='"+value+"'";
            }

            this.query+= fvstring;
        }
    }

    /**
     * extrage un string de forma `camp_1`, `camp_2`, ... 'camp_n' din HashMap-ul parametru
     * 
     * @param assoc		valorile de extras (forma camp=valoare)
     * @return String	string-ul rezultat
     */
    public String getFields(HashMap<?, ?> assoc){
        Iterator<?> iterator;
        Entry<?, ?> entry;

        String fields = "";

        if((assoc != null) && (!assoc.isEmpty())){
            String field;
            Set<?> fieldsValues = assoc.entrySet();

            iterator = fieldsValues.iterator();
            while(iterator.hasNext()){
                 entry = (Entry<?, ?>)iterator.next();

                 //-------sanitizeaza string-urile------------//
                 field = StringEscapeUtils.escapeSql(entry.getKey().toString());

                 fields += fields.length() == 0 ? "`"+ field +"`" : ", `"+ field +"`";
            }
        }

        return fields;
    }

    /**
     * extrage un string de forma `valoare_1`, `valoare_2`, ... 'valoare_n' din HashMap-ul parametru
     * 
     * @param assoc		valorile de extras (forma camp=valoare)
     * @return String	string-ul rezultat
     */
    public String getValues(HashMap<?, ?> assoc){
        Iterator<?> iterator;
        Entry<?, ?> entry;

        String values = "";

        if((assoc != null) && (!assoc.isEmpty())){
            String value;
            Set<?> fieldsValues = assoc.entrySet();

            iterator = fieldsValues.iterator();
            while(iterator.hasNext()){
                 entry = (Entry<?, ?>)iterator.next();

                 //-------sanitizeaza string-urile------------//
                 value = StringEscapeUtils.escapeSql(entry.getValue().toString());

                 values += values.length() == 0 ? "'"+ value +"'" : ", '"+ value +"'";
            }
        }

        return values;
    }

    /**
     * efectueaza o cerere sql de tip SELECT si returneaza rezultatul sub forma unui ResultSet
     * 
     * Exemplu:
     * <pre>
     * 		HashMap where= new HashMap();
     * 		
     * 		where.put("id", "10");
     * 
     * 		ResultSet data = Database.select("id, full_name, email", "users", where, null, null, null, true);
     * 		if(data != null){
     * 			...
     * 		}
     * 		else{
     * 			...a aparut o exceptie SQL, cererea nu este corecta ...
     * 		}
     * </pre>
     * 
     * @param fields		campurile de selectat	
     * @param table			tabelul din care se face selectia
     * @param where			colectia de conditii si (de forma camp=valoare)
     * @param or			colectia de conditii sau (de forma camp=valoare)
     * @param order			conditia de ordonare
     * @param othercond		alte conditii optionale (gen LIMIT)
     * @param escapecond	sanitizare string-uri sau nu
     * 
     * @return ResultSet	obiectul rezultat sau null, in cazul aparitiei unei exceptii de tipul SQLException
     * 
     * @throws DBConnectionLostException este aruncata in cazul aparitiei unei exceptii de comunicatie cu  SGBD-ul 
     */
    public ResultSet select(String fields, String table, HashMap<?, ?> where, HashMap<?, ?> or, String order, String othercond, boolean escapecond) throws DBConnectionLostException{
        
        this.query = "SELECT "+ StringEscapeUtils.escapeSql(fields) +" FROM "+ StringEscapeUtils.escapeSql(table) ;

        this.addConditions("WHERE", where, "AND");
        this.addConditions("OR", or);

        if((order != null) && (order.length() > 0)){
            this.query+= " ORDER BY " + StringEscapeUtils.escapeSql(order);
        }

        if((othercond != null) && (othercond.length() > 0)){
        	if(escapecond == true){
        		this.query+= StringEscapeUtils.escapeSql(othercond);
        	}
        	else{
        		this.query+= othercond;
        	}
        }

        //---executa cererea sql----------------//
        try {
            this.Statement = this.DBConnection.prepareStatement(query);
            this.Result = this.Statement.executeQuery();

            return this.Result;

        }catch(CommunicationsException ce){
        	Application.Log.write(ce.toString(), EventLogger.ERROR);
        	throw new DBConnectionLostException();
        } catch (SQLException sqlex) {
        	this.error = sqlex.toString();
            Application.Log.write(sqlex.toString(), EventLogger.ERROR);
        }

        return null;
    }

    /**
     * efectueaza o cerere sql de tip UPDATE; 
     * rezultatul indica daca s-au semnalat sau nu exceptii de tipul SQLException
     * 
     * Exemplu:
     * <pre>
     * 		HashMap values = new HashMap();
     * 
     * 		values.put("first_name", "Klein");
     * 		values.put("last_name",  "Calvin");
     * 
     * 		HashMap where = new HashMap();
     * 
     * 		where.put("id_user", "19");		
     * 
     * 		boolean updated = Database.update("users", values, where, null, null);
     * </pre>
     * 
     * @param table		tabelul in care se face update
     * @param values	valorile care se modifica (de forma camp=valoare)
     * @param where		colectia de conditii si (de forma camp=valoare)
     * @param or		colectia de conditii sau (de forma camp=valoare)
     * @param othercond alte conditii suplimetare
     * @return boolean	daca operatia de update s-a incheiat cu success 
     * 
     * @throws DBConnectionLostException este aruncata in cazul aparitiei unei exceptii de comunicatie cu  SGBD-ul
     */
    public boolean update(String table, HashMap<?, ?> values, HashMap<?, ?> where, HashMap<?, ?> or, String othercond) throws DBConnectionLostException{
        this.query = "UPDATE `"+ StringEscapeUtils.escapeSql(table) +"` SET ";
        
        this.addFieldsValues(table, values);
        this.addConditions("WHERE", where, "AND");
        this.addConditions("OR", or);
        
        if((othercond != null) && (othercond.length() > 0)){
            this.query+= StringEscapeUtils.escapeSql(othercond);
        }

        try {

            this.Statement = this.DBConnection.prepareStatement(query);
            this.Statement.executeUpdate();

            //--- daca nu s-au semnalat erori pana in acest punct operatia s-a incheiat cu succes----------//
            return true;
            
        }catch(CommunicationsException ce){
        	Application.Log.write(ce.toString(), EventLogger.ERROR);
        	throw new DBConnectionLostException();
        	
        } catch (SQLException ex) {
            this.error = ex.toString();
            Application.Log.write(ex.toString(), EventLogger.ERROR);
        }

        return false;
    }

    /**
     * efectueaza o cerere sql de tip INSERT; 
     * rezultatul indica daca s-au semnalat sau nu exceptii de tipul SQLException
     * 
     * Exemplu:
     * <pre>
     *		HashMap values = new HashMap();
     * 
     * 		values.put("first_name", "Anthony");
     * 		values.put("last_name",  "Hopkins");	
     * 		values.put("email"	  ,  "ahopkins@hismail.com");	
     * 
     * 		boolean inserted = Database.insert("users", values);
     * </pre>
     * 
     * @param table		tabelul in care se face inserare
     * @param values	valorile ce vor fi inserate in tabel
     * 
     * @return boolean	daca operatia s-a incheiat fara erori 
     * 
     * @throws DBConnectionLostException este aruncata in cazul aparitiei unei exceptii de comunicatie cu  SGBD-ul
     */
    public boolean insert(String table, HashMap<?, ?> values) throws DBConnectionLostException{

        String Fields = this.getFields(values);
        String Values = this.getValues(values);

        this.query = "INSERT INTO `"+ StringEscapeUtils.escapeSql(table) +"` ("+ Fields +") VALUES ("+ Values +")";
        
        try {
            this.Statement = this.DBConnection.prepareStatement(query);
            this.Statement.executeUpdate();

            return true;
            
        }catch(CommunicationsException ce){
        	Application.Log.write(ce.toString(), EventLogger.ERROR);
        	throw new DBConnectionLostException();
        	
        } catch (SQLException ex) {
            this.error = ex.toString();
            Application.Log.write(ex.toString(), EventLogger.ERROR);
        }

        return false;
    }

    /**
     * efectueaza o cerere sql de tip DELETE; rezultatul indica daca s-au semnalat sau nu exceptii de tipul SQLException
     * Exemplu:
     * <pre>
     * 		HashMap where = new HashMap();
     * 
     * 		where.put("id_user", "123");
     * 
     * 		boolean inserted = Database.delete("users", where, null, null);
     * </pre>
     * 
     * @param table		tabelul din care se sterge inregistrarea(le)
     * @param where		colectia de conditii si (de forma camp=valoare)
     * @param or		colectia de conditii sau (de forma camp=valoare)
     * @param othercond alte conditii suplimetare
     * @return boolean	daca operatia s-a incheiat fara erori 
     * 
     * @throws DBConnectionLostException este aruncata in cazul aparitiei unei exceptii de comunicatie cu  SGBD-ul
     */
    public boolean delete(String table, HashMap<?, ?> where, HashMap<?, ?> or, String othercond) throws DBConnectionLostException{

        this.query = "DELETE FROM `"+ StringEscapeUtils.escapeSql(table) +"` ";

        this.addConditions("WHERE", where, "AND");
        this.addConditions("OR", or);

        if((othercond != null) && (othercond.length() > 0)){
            this.query+= StringEscapeUtils.escapeSql(othercond);
        }


        try {
            this.Statement = this.DBConnection.prepareStatement(query);
            this.Statement.executeUpdate();

            return true;
        }catch(CommunicationsException ce){
        	Application.Log.write(ce.toString(), EventLogger.ERROR);
        	throw new DBConnectionLostException();
             
        } catch (SQLException ex) {
           this.error = ex.toString();
           Application.Log.write(ex.toString(), EventLogger.ERROR);
        }

        return false;
    }

    /**
     * returneaza ultimul ID inserat in tabel, dupa o cerere de tip INSERT;
     * 
     * @param table		numele tabelului
     * @return Integer	valoarea id-ului (cheii primare, autoincrement) inserat, sau 0 in cazul in care nu exista inregistrari, sau aparitiei unei exceptii SQL
     * 
     * @throws DBConnectionLostException este aruncata in cazul aparitiei unei exceptii de comunicatie cu SGBD-ul
     */
    public Integer last_insert_id(String table) throws DBConnectionLostException{
        this.select("LAST_INSERT_ID() as `id`", table, null, null, null, " WHERE 1", false);
        
        try {
            if (this.Result.first()) {
                return this.Result.getInt("id");
            }
        } catch (SQLException ex) {
            this.error = ex.toString();
            Application.Log.write(ex.toString(), EventLogger.ERROR);
        }

        return 0;
    }

    /**
     * contruieste partea de filtrare a unei cereru SQL
     * Exemplu:
     * <pre>
     * 		String[] operators = new String[]{Database.FILTEROPERATOR_CONTAINS, Database.FILTEROPERATOR_STARTSWITH};
     * 		String	 condition = Database.SELECTCONDITION_OR;
     * 
     * 		HashMap values = new HashMap();
     * 
     * 		values.put("car_color", "red");
     * 
     * 		String filterSQLPart = Database.makeSQLFilterQuery(operators, condition, values);
     * </pre>
     * 
     * @see MySqlDatabase.filter(String, String[], String, HashMap);
     * 
     * @param operators			operatorii utilizati (dintre MySqlDatabase.FILTEROPERATOR_...)
     * @param selectCondition	conditia de selectie (OR sau AND)
     * @param values			valorile pa baza carora se face selectia (de forma camp=valoare)
     * @return					string-ul rezultat 
     */
    public String makeSQLFilterQuery(String[] operators, String selectCondition, HashMap<?, ?> values){
    	Iterator<?> iterator;
        Entry<?, ?>  entry;
        
        if((values != null) && (!values.isEmpty())){

            String field;
            String value;

            String  SQLQuery = "";
            String  startcond= "";
            
            Set<?>  whereConditions = values.entrySet();

            iterator = whereConditions.iterator();
            while(iterator.hasNext()){
                 entry = (Entry<?, ?>)iterator.next();

                 //-------sanitizeaza string-urile------------//
                 field = StringEscapeUtils.escapeSql(entry.getKey().toString());
                 value = StringEscapeUtils.escapeSql(entry.getValue().toString());
                 
                 if(operators.length > 0){
                 	for(int i=0; i<operators.length; i++){
                 		
                 		if(SQLQuery.length() == 0){
                 			SQLQuery	+=" WHERE ";
                 			startcond	= "";
                 		}
                 		else{
                 			startcond	= selectCondition;
                 		}
                 		
                 		if(operators[i].equals(this.FILTEROPERATOR_CONTAINS)){
                 			SQLQuery += (startcond+ "`"+ field +"` LIKE '%"+ value + "%'");
                 		}
                 		if(operators[i].equals(this.FILTEROPERATOR_STARTSWITH)){
                 			SQLQuery += (startcond+ "`"+ field +"` LIKE '"+ value + "%'");
                 		}
                 		if(operators[i].equals(this.FILTEROPERATOR_ENDSWITH)){
                 			SQLQuery += (startcond+ "`"+ field +"` LIKE '%"+ value + "'");
                 		}
                 		if(operators[i].equals(this.FILTEROPERATOR_SMALLER)){
                 			SQLQuery += (startcond+ "`"+ field +"`<'"+ value + "'");
                 		}
                 		if(operators[i].equals(this.FILTEROPERATOR_SMALLEROREQUAL)){
                 			SQLQuery += (startcond+ "`"+ field +"`<='"+ value + "'");
                 		}
                 		if(operators[i].equals(this.FILTEROPERATOR_BIGGER)){
                 			SQLQuery += (startcond+ "`"+ field +"`>'"+ value + "'");
                 		}
                 		if(operators[i].equals(this.FILTEROPERATOR_BIGGEROREQUAL)){
                 			SQLQuery += (startcond+ "`"+ field +"`>='"+ value + "'");
                 		}
                 	}
                 }
            }
            
            return SQLQuery;
        }
        
        return null;
    }
    
    /**
     * returneaza rezultatul unei filtrari din baza de date;
     * Exemplu:
     * <pre>
     * 		String[] operators = new String[]{Database.FILTEROPERATOR_CONTAINS, Database.FILTEROPERATOR_STARTSWITH};
     * 		String	 condition = Database.SELECTCONDITION_OR;
     * 
     * 		HashMap values = new HashMap();
     * 
     * 		values.put("car_color", "red");
     * 
     * 		ResultSet rows = Database.filter("cars", operators, condition, values);
     * 		if(rows != null){
     * 			... extrage datele ...
     * 		}
     * 		else{
     * 			... a aparut o eroare sql ...
     * 		}
     * </pre>
     * 
     * @param table				tabelul in care se face filtrarea
     * @param operators			operatorii utilizati (dintre MySqlDatabase.FILTEROPERATOR_...)
     * @param selectCondition	conditia de selectie (OR sau AND, dintre dintre MySqlDatabase.SELECTCONDITION_...)
     * @param values			valorile pa baza carora se face selectia (de forma camp=valoare)
     * @return					obiectul rezultat, sau null daca query-ul sql generat nu este valid
     * 
     * @throws DBConnectionLostException este aruncata in cazul aparitiei unei exceptii de comunicatie cu SGBD-ul
     */
    public ResultSet filter(String table, String[] operators, String selectCondition, HashMap<?, ?> values) throws DBConnectionLostException{

    	if(selectCondition.equals(this.SELECTCONDITION_AND)){
    		;
    	}
    	
    	else if(selectCondition.equals(this.SELECTCONDITION_OR)){
    		;
    	}
    	else{
    		return null;
    	}
    	
        String SQLQuery = this.makeSQLFilterQuery(operators, selectCondition, values);
            
        if((SQLQuery != null) && (SQLQuery.length() > 0)){
        	return this.select("*", table, null, null, null, SQLQuery, false);
        }
    	
    	return null;
    }
    
    public static String[] getColumnNames(ResultSet result) throws SQLException {
        if (result == null) {
          return null;
        }
        
        ResultSetMetaData rsMetaData = (ResultSetMetaData) result.getMetaData();
        int colsNr = rsMetaData.getColumnCount();
        
        String[] colNames = new String[colsNr + 1];
        
        // get the column names; column indexes start from 1
        for (int i=1; i <= colsNr; i++) {
        	colNames[i-1] = rsMetaData.getColumnName(i);
        }
        
        return colNames;
    }
    
}
